#!/usr/bin/env python3
# -*- coding: utf-8 -*-
' main entry '
__author__ = 'pingwu you'

'''
因为打开文件可以使用with，而免去手动关闭文件句柄，写sqlite3也这样做却是报错，发现原来要包装一下，
sqlite3给的对象不支持with（其实这样写也有弊端，毕竟conn是可以选择commit的） 
Connection objects can be used as context managers that automatically commit or rollback transactions.
 In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:
如果一个对象没有实现上下文，我们就不能把它用于with语句。这个时候，可以用closing()来把该对象变为上下文对象。
'''
import sqlite3
def init():    
    conn=sqlite3.connect("db/sqllite.db")
    cs =conn.cursor()
    cs.execute('create table student (id varchar(20) primary key, name varchar(20))')
    cs.execute('insert into student (id, name) values (\'4\', \'Michael\')')

    #不需要以下部分，类似java的事务管理器对于jdbcTemplate的自动管理
    # with sqlite3.connect("db/sqllite.db") as conn:
    #     with closing(conn.cursor()) as cs:
    #         cs.execute('create table user (id varchar(20) primary key, name varchar(20))')
    #         cs.execute('insert into user (id, name) values (\'1\', \'Michael\')')
    #         conn.commit()
    # try:
    #     conn=sqlite3.connect("db/sqllite.db")
    #     cs =conn.cursor()
    #     #cs.execute('create table user (id varchar(20) primary key, name varchar(20))')
    #     cs.execute('insert into user (id, name) values (\'3\', \'Michael\')')       
    #     conn.commit()
    # finally:
    #     if(cs):
    #         cs.close()
    #     if(conn):
    #         conn.close()

def query():
    conn=sqlite3.connect("db/sqllite.db")
    cs =conn.cursor()
    cs.execute('select * from user where id=?', ('2',))
    ls=cs.fetchall()
    print(ls)

    #不需要以下部分，类似java的事务管理器对于jdbcTemplate的自动管理
    # try:
    #     conn=sqlite3.connect("db/sqllite.db")
    #     cs =conn.cursor()
    #     cs.execute('select * from user where id=?', ('2',))
    #     ls=cs.fetchall()
    #     print(ls)
    # finally:
    #     if(cs):
    #         cs.close()
    #     if(conn):
    #         conn.close()   